import requests
from flask import Blueprint, render_template, jsonify, request
from sqlalchemy import func

from applications.extensions import db
from applications.common.utils.rights import authorize
from applications.models.bigScreenData import AffectedArea, AgriculturalYield, PlantingArea

bp = Blueprint('bigscreen', __name__, url_prefix='/bigscreen')


@bp.get('/index')
@authorize("bigscreen:main")
def main():
    # 获取所有存在的年份列表（去重）
    years = db.session.query(
        AffectedArea.year
    ).distinct().order_by(
        AffectedArea.year.desc()  # 降序排序
    ).all()
    years = [year[0] for year in years]
    return render_template('bigscreen/index.html', years=years)


@bp.get('/year')
@authorize("bigscreen:main")
def year():
    # 获取所有存在的年份列表（去重）
    years = db.session.query(
        AffectedArea.year
    ).distinct().order_by(
        AffectedArea.year.desc()  # 降序排序
    ).all()
    print(years)


@bp.post('/agriculturalyield')
@authorize("bigscreen:main")
def agriculturalyield():
    year = request.json.get('year')
    # print(year)
    # 使用窗口函数分组取最大值
    subquery = db.session.query(
        AgriculturalYield.province_name,
        AgriculturalYield.value,
        AgriculturalYield.indicator,
        func.row_number().over(
            partition_by=AgriculturalYield.province_name,
            order_by=AgriculturalYield.value.desc()
        ).label('rn')
    ).filter(
        AgriculturalYield.year == year

    ).subquery()
    # 通过列名访问结果
    results = db.session.query(subquery).filter(subquery.c.rn == 1).all()
    data = [{
        "name": row.province_name[
                :3] if row.province_name == "内蒙古自治区" or row.province_name == "黑龙江省" else row.province_name[
                                                                                                   :2],

        "value": row.value,
        "indicator": row.indicator
    } for row in results]
    return jsonify(data)


@bp.post('/agriculturalyield/top10')
@authorize("bigscreen:main")
def agriculturalyield_top10():
    year = request.json.get('year')
    results = (db.session.query(
        func.sum(AgriculturalYield.value).label('total_value'),  # 使用聚合函数
        AgriculturalYield.indicator
    ).filter(
        AgriculturalYield.year == year
    ).group_by(
        AgriculturalYield.indicator  # 仅按指标分组
    ).order_by(
        func.sum(AgriculturalYield.value).desc()  # 按聚合值排序
    ).limit(10).all())

    data = [{
        "value": row.total_value,
        "indicator": row.indicator[:2]
    } for row in results]
    return jsonify(data)


@bp.post('/plantingArea/top10')
@authorize("bigscreen:main")
def plantingArea_top10():
    year = request.json.get('year')
    results = (db.session.query(
        func.sum(PlantingArea.value).label('total_value'),  # 使用聚合函数
        PlantingArea.indicator
    ).filter(
        PlantingArea.year == year
    ).group_by(
        PlantingArea.indicator  # 仅按指标分组
    ).order_by(
        func.sum(PlantingArea.value).desc()  # 按聚合值排序
    ).limit(10).all())

    data = [{
        "value": row.total_value,
        "indicator": row.indicator[:2]
    } for row in results]
    return jsonify(data)


# 产量全部
@bp.post('/agriculturalYield/all')
@authorize("bigscreen:main")
def AgriculturalYield_all():
    year = request.json.get('year')
    results = (db.session.query(
        func.sum(AgriculturalYield.value).label('total_value'),  # 使用聚合函数
        AgriculturalYield.indicator
    ).filter(
        AgriculturalYield.year == year
    ).group_by(
        AgriculturalYield.indicator  # 仅按指标分组
    ).order_by(
        func.sum(AgriculturalYield.value).desc()  # 按聚合值排序
    ).all())
    data = [{
        "value": row.total_value if row.total_value is not None else 0,  # 空值转0
        "indicator": row.indicator[:2]
    } for row in results]
    return jsonify(data)


# 播种面积全部
@bp.post('/plantingArea/all')
@authorize("bigscreen:main")
def plantingArea_all():
    year = request.json.get('year')
    results = (db.session.query(
        func.sum(PlantingArea.value).label('total_value'),  # 使用聚合函数
        PlantingArea.indicator
    ).filter(
        PlantingArea.year == year
    ).group_by(
        PlantingArea.indicator  # 仅按指标分组
    ).order_by(
        func.sum(PlantingArea.value).desc()  # 按聚合值排序
    ).all())

    data = [{
        "value": row.total_value if row.total_value is not None else 0,  # 空值转0
        "indicator": row.indicator[:2]
    } for row in results]
    return jsonify(data)


# 受灾
@bp.post('/affected_area/all')
@authorize("bigscreen:main")
def AffectedArea_all():
    year = request.json.get('year')
    results = (db.session.query(
        func.sum(AffectedArea.value).label('total_value'),  # 使用聚合函数
        AffectedArea.indicator
    ).filter(
        AffectedArea.year == year
    ).group_by(
        AffectedArea.indicator  # 仅按指标分组
    ).order_by(
        func.sum(AffectedArea.value).desc()  # 按聚合值排序
    ).all())

    data = [{
        "value": row.total_value if row.total_value is not None else 0,  # 空值转0
        "indicator": row.indicator[:2]
    } for row in results]
    return jsonify(data)


# 受灾 产量 播种对比
@bp.post('/contrast')
@authorize("bigscreen:main")
def contrast():
    data = []
    year = request.json.get('year')
    # 并行查询三个数据源
    yield_total = db.session.query(
        func.coalesce(func.sum(AgriculturalYield.value), 0)
    ).filter(AgriculturalYield.year == year).scalar()

    affected_total = db.session.query(
        func.coalesce(func.sum(AffectedArea.value), 0)
    ).filter(AffectedArea.year == year).scalar()

    planting_total = db.session.query(
        func.coalesce(func.sum(PlantingArea.value), 0)
    ).filter(PlantingArea.year == year).scalar()

    data = dict(sorted({
                           "种植面积(千公顷)": round(float(planting_total), 2),
                           "产量(万吨)": round(float(yield_total), 2),
                           "受灾面积(千公顷)": round(float(affected_total), 2)
                       }.items(), key=lambda item: item[0], reverse=True))
    return jsonify(
        data
    )
